USE SysBARC
GO

SELECT *
FROM UserInfo
ORDER BY id
SELECT *
FROM Role
SELECT *
FROM UserInfoRelationshipRole

-- 查询用户的角色信息
SELECT u.nickName, r.name
FROM (
       SELECT *
       FROM UserInfo
       --WHERE uuid = 'AE1D1BDB-F780-4118-8B00-6CC6CD017004'
     ) AS u
       LEFT OUTER JOIN UserInfoRelationshipRole AS u_r ON u.uuid = u_r.user_id
       LEFT JOIN Role r on r.uuid = u_r.role_id


SELECT *
FROM UserInfo AS U
ORDER BY id
SELECT *
FROM Menu AS M
ORDER BY id
SELECT *
FROM Permission AS P
ORDER BY id

-- 角色权限关系表
SELECT *
FROM RoleRelationshipPermission

-- 查询用户权限标识
SELECT u.nickName, r.name,P.name,p.comment
FROM (
       SELECT *
       FROM UserInfo
       -- 不会淹死的鱼จุ๊บ
       WHERE uuid = 'AE1D1BDB-F780-4118-8B00-6CC6CD017004'
     ) AS u
       LEFT OUTER JOIN UserInfoRelationshipRole AS u_r ON u.uuid = u_r.user_id
       LEFT JOIN Role r on r.uuid = u_r.role_id
       LEFT JOIN RoleRelationshipPermission RRP on r.uuid = RRP.role_id
       LEFT JOIN Permission P on RRP.permission_id = P.uuid

-- 查询菜单权限标识

SELECT M.*,P.name,P.comment FROM  Menu M
LEFT JOIN  MenuRelationshipPermission MRP on M.uuid = MRP.menu_id
LEFT JOIN Permission P on p.uuid = MRP.permission_id

with tmpTable
       as
       (
-- 1、根节点
         select * from Menu where pid  is null
         union all
-- 2、递归条件
         select a.* from Menu a inner join Menu b on a.pid = b.uuid
       )
select * from tmpTable ORDER BY id;
